Click Here!
home account info subscribe login search My ITKnowledge FAQ/help site map contact us


 
Brief Full
 Advanced
      Search
 Search Tips
To access the contents, click the chapter and section titles.

Oracle Performance Tuning and Optimization
(Publisher: Macmillan Computer Publishing)
Author(s): Edward Whalen
ISBN: 067230886x
Publication Date: 04/01/96

Bookmark It

Search this book:
 
Previous Table of Contents Next


The V$LIBRARYCACHE table contains statistics on how well you are using the library cache. The important columns to view in this table are PINS and RELOADS:

  PINS: The number of times the item in the library cache was executed.
  RELOADS: The number of times the library cache missed and the library object was reloaded.

A few number of reloads relative to the number of executions indicates a high cache-hit rate. To get an idea of the total number of cache misses, use this statement:

SQL> SELECT SUM(reloads) "Cache Misses",
  2   SUM(pins) "Executions",
  3  100 * ( SUM(reloads) / SUM(pins) ) "Cache Miss Percent"
  4  FROM v$librarycache;

Cache Misses Executions Cache Miss Percent
-------        --------       ------------
          9         2017         .44620724

The preceding example indicates that a sum of 2,017 SQL statements, PL/SQL blocks, and object definitions were accessed and only 9 were reloaded because they had aged out of the library cache. This means that only 0.44 percent of these statements resulted in reparsing—a very good cache-hit ratio.

To look at the cache hits based on the types of statements, use the following statement:

SQL> SELECT namespace,
   2  reloads "Cache Misses",
   3   pins "Executions"
   4  FROM v$librarycache;

NAMESPACE       Cache Misses Executions
--------        --------       --------
SQL AREA                  4        1676
TABLE/PROCEDURE            5        309
BODY                      0           0
TRIGGER                   0           0
INDEX                     0          21
CLUSTER                   0          15
OBJECT                    0           0
PIPE                      0           0

8 rows selected.

The total number of reloads should be near zero. If you see more than 1 percent library cache misses, you should take action: reduce the cache misses by writing identical SQL statements or by increasing the size of the library cache.

You should be able to reduce the library cache misses by increasing the amount of memory available for the library cache. Do this by increasing the Oracle tunable parameter SHARED_POOL_SIZE. You may also need to increase the number of cursors available for a session by increasing the Oracle parameter OPEN_CURSORS.

Be careful not to increase the amount of memory required beyond that set aside by the operating system. Any paging or swapping caused by that offsets any advantage you get from the library cache.

If you have plenty of memory, you may be able to speed access to the shared SQL areas by setting the Oracle initialization parameter CURSOR_SPACE_FOR_TIME to TRUE. When this parameter is set to TRUE, it specifies that a shared SQL area cannot be deallocated until all the cursors associated with it are closed.

If CURSOR_SPACE_FOR_TIME is TRUE, it is not necessary for Oracle to check to see whether the SQL statement is in the library cache because it cannot be deallocated as long as the cursor is open. If memory is scarce on your system, do not set this parameter. If the value is TRUE and there is no space in the shared pool for a new SQL statement, an error is returned, halting the application.

Now that you have reviewed the advantages of using stored procedures, functions, and packages, the following sections present some specifics on how to use them.

Procedures and Functions

Procedures and functions are similar. In fact, they are so much alike that, throughout this book (except for this chapter), they have been referred to indiscriminately as stored procedures. Procedures and functions are subprograms made up of PL/SQL code that take a set of parameters given to them by the calling program and perform a set of actions. The difference between a procedure and a function is that a function can include a return value. Both functions and procedures can modify and return data passed to them as a parameter. Usually, procedures are used unless only one return value is needed. A procedure or function that has been stored in the library cache is referred to as a stored procedure or a stored function.

A stored procedure or stored function has the following properties:


Property Comments

Has a name This is the name by which the stored procedure or function is called and referenced.
Takes parameters These are the values sent to the stored procedure or function from the application.
Returns values A stored procedure or function can return one or more values based on the purpose of the procedure or function.
Stored in data dictionary The stored procedure or function is stored in a parsed form in the data dictionary.


Previous Table of Contents Next


Products |  Contact Us |  About Us |  Privacy  |  Ad Info  |  Home

Use of this site is subject to certain Terms & Conditions, Copyright © 1996-2000 EarthWeb Inc.
All rights reserved. Reproduction whole or in part in any form or medium without express written permission of EarthWeb is prohibited.